package com.compty.paint.manager;

import java.io.Serializable;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.compty.core.hibernate.HibernateEntityDao;
import com.compty.core.page.Page;
import com.compty.paint.domain.AuditState;
import com.compty.paint.domain.CatalogType;
import com.compty.paint.domain.Exhibition;
import com.compty.paint.domain.Gallery;
import com.compty.paint.domain.ImageInfo;
import com.compty.paint.domain.Painting;
import com.compty.paint.domain.UploadState;
import com.compty.paint.exception.OrderExistException;
import com.compty.paint.util.Constants;

public class PaintingManager extends HibernateEntityDao<Painting>{
	
	private UploadStateManager uploadStateManager;
	private AuditStateManager auditStateManager;
	private GoodsManager goodsManager;
	private CatalogTypeManager catalogTypeManager;

	public void setUploadStateManager(UploadStateManager uploadStateManager) {
		this.uploadStateManager = uploadStateManager;
	}
	
	public void setAuditStateManager(AuditStateManager auditStateManager) {
		this.auditStateManager = auditStateManager;
	}
	
	public void setGoodsManager(GoodsManager goodsManager) {
		this.goodsManager = goodsManager;
	}

	public void setCatalogTypeManager(CatalogTypeManager catalogTypeManager) {
		this.catalogTypeManager = catalogTypeManager;
	}

	/**
	 * 删除作品的同时删除展览的外键关联
	 * @throws OrderExistException 
	 */
	 @SuppressWarnings("unchecked")
	public void deleteById(Serializable id) throws OrderExistException {
		 
		 /********************  解除展览关联关系  begin **********************/
		 String hql = "select e from Exhibition e inner join  e.paintings as p where p.id = ?";
		 
		 Painting p = get(id);
		 
		 List list = super.find(hql, id);
		 
		 if(list != null && list.size()>0 ){
			 Exhibition e = (Exhibition) list.get(0);
			 e.getPaintings().remove(p);
			 int totalCount = e.getTotalCount();
			 totalCount = totalCount -1;
			 totalCount = totalCount > 0 ? totalCount : 0;
			 e.setTotalCount(totalCount);
			 super.save(e);
		 }
		 /********************  解除展览关联关系  end **********************/
		 
		 /******************** 解除画廊关联关系 begin **********************/
		 hql = "select g from Gallery g inner join  g.paintings as p where p.id = ?";
		 
		 list = super.find(hql, id);
		 
		 if(list != null && list.size()>0 ){
			 Gallery e = (Gallery) list.get(0);
			 e.getPaintings().remove(p);
			 super.save(e);
		 }
		 /******************** 解除画廊关联关系 end **********************/
		 
		 goodsManager.removeByPaintingId(id);//删除作品对应的商品
		 
		 deletePainting(p);
	 }
	 
	 
	@SuppressWarnings("unchecked")
	public void deletePainting(Painting painting){
		 ImageInfo  imageInfo = painting.getImageInfo();
		 Long imageId = imageInfo.getId();
		 
		 String hql = "from Painting where imageInfo.id = ?";
		 Session session = getSession();
		 Query query = session.createQuery(hql);
		 query.setParameter(0, imageId);
		 List  list = query.list();
		 if(list != null && list.size()>1){
			String hql2 = "update Painting set imageInfo = null where id = ? ";
			query = session.createQuery(hql2);
			query.setParameter(0, painting.getId());
			query.executeUpdate();
			
			 String hql3 = "delete Painting where id= ?";
			 query = session.createQuery(hql3);
			 query.setParameter(0, painting.getId());
			 query.executeUpdate();
			 
			 releaseSession(session);
		 }else{
			 super.removeById(painting.getId());
		 }
	 }
	
	/**
	 *@Todo:分页查找
	 *@author:zhuqh
	 *@CreateTime:2011-8-18 下午04:03:40
	 *@param painttype 画家类型 1:客座画家 2:名家
	 * @param curPage 当前页码
	 * @param pageSize 显示条数
	 */
	public Page findPaintings(String painttype,int curPage, int pageSize){
		
        UploadState  uploadState = uploadStateManager.findUniqueBy("english_name", Constants.Upload_State_Init); //上传状态正常
        AuditState auditState = auditStateManager.findUniqueBy("english_name", Constants.Audit_State_Pass);  //审核状态通过
		
		String hql = " from Painting p where p.painter.type = ? and p.uploadState= ? " +
				" and p.auditState= ? order by p.id desc ";
		
		return super.pagedQuery(hql, curPage, pageSize, painttype,uploadState,auditState);
	}

	/**
	 *@Todo:根据画家ID查找其可用(状态正常)的所有作品
	 *@CreateTime:2011-9-6 上午10:02:35
	 * @param painterId 画家ID
	 */
	@SuppressWarnings("unchecked")
	public List<Painting> findAvailablePaintingsByPainter(Long painterId) {
		
	    UploadState  uploadState = uploadStateManager.findUniqueBy("english_name", Constants.Upload_State_Init); //上传状态正常
	    AuditState auditState = auditStateManager.findUniqueBy("english_name", Constants.Audit_State_Pass);  //审核状态通过
		
		String hql = "from Painting where uploadState= ?  and auditState= ? and  painter.id = ?";
		
		return super.find(hql,uploadState,auditState, painterId);
	}

	/**
	 *@Todo:根据画家ID查找其最新作品
	 *@CreateTime:2011-9-22 上午10:02:35
	 * @param painterId 画家ID
	 */
	public Painting findNewestPaintingsByPainter(Long painterId) {
		String hql = "from Painting where painter.id = ? and auditstate_id = 3 and uploadstate_id = 1  order by id desc ";
		
		return super.findUnique(hql, painterId);
	}
	
	/**
	 * 根据画家ID，及最后修改时间查找作品集合
	 * @param painterId 画家ID
	 * @param beginTime 最后修改时间
	 */
	@SuppressWarnings("unchecked")
	public List<Painting>  findPaintingsByPainter(Long painterId, Date beginTime ) {
		
		String hql = "from Painting where painter.id = ? and modifyTime >= ? order by id desc ";
		if( beginTime == null){
			hql = "from Painting where painter.id = ? order by id desc ";
		}
		if(beginTime == null ){
			return super.find(hql, painterId);
		}else{
			return super.find(hql, painterId,beginTime);
		}
	}
	
	/**
	* @Description: 查询某画家从起始时间至今更新作品数量
	* @param painterId 画家ID
	* @param beginTime 起始时间
	 */
	public int findAvailablePaintingsCountByPainter(Long painterId, Date beginTime) {
		int count = 0 ;
		
	    UploadState  uploadState = uploadStateManager.findUniqueBy("english_name", Constants.Upload_State_Init); //上传状态正常
	    AuditState auditState = auditStateManager.findUniqueBy("english_name", Constants.Audit_State_Pass);  //审核状态通过
		
		String hql = "select count(*) from Painting where uploadState= ?  and auditState= ? and painter.id = ? and uploadTime >= ?";
		if( beginTime == null){
			hql = "select count(*) from Painting where uploadState= ?  and auditState= ? and painter.id = ?";
		}
		
		if( beginTime == null){
			Query query = super.createQuery(hql, uploadState,auditState,painterId);
			count = Integer.parseInt(query.list().iterator().next().toString());
			return count;
		}else{
			Query query = super.createQuery(hql, uploadState,auditState,painterId,beginTime);
			count = Integer.parseInt(query.list().iterator().next().toString());
			return count;
		}
	}

	
	/**
	 *@Todo:分页查找名家作品(随机)
	 *@CreateTime:2011-12-22 上午09:55:17
	 * @param type 作品类型(1:油画 2:国画 null:不指定类型)
	 */
	public Page findPaintingsByPageOfPainterStarByRandom(final String type,final int curPage,final int pageSize) {
		
		List list  =  (List)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				StringBuilder sql = new StringBuilder("select p.* from t_painting p,t_painter p2 where p.uploadstate_id = 1  and p.auditstate_id= 3 and p.painterid=p2.id ");
				if("1".equals(type)){//油画
					sql.append(" and ( p2.decade = 2  or p2.decade = 4)");
				}
				else if("2".equals(type)){//国画
					sql.append(" and ( p2.decade = 1  or p2.decade = 3 or p2.decade = 5 )");
				}
				
				sql.append(" order by rand() ");
				
				SQLQuery query = session.createSQLQuery(sql.toString());
				query.setFirstResult((curPage-1)*pageSize);
				query.setMaxResults(pageSize);
				
				List list = query.addEntity(Painting.class).list();
				
				return list;
			}
		});
		
		Integer totalCount  =  (Integer)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				StringBuilder sql = new StringBuilder("select  count(*) as totalCount  from t_painting p,t_painter p2 where p.uploadstate_id = 1  and p.auditstate_id= 3 and p.painterid=p2.id ");
				if("1".equals(type)){//油画
					sql.append(" and ( p2.decade = 2  or p2.decade = 4)");
				}
				else if("2".equals(type)){//国画
					sql.append(" and ( p2.decade = 1  or p2.decade = 3 or p2.decade = 5 )");
				}
				
				SQLQuery query = session.createSQLQuery(sql.toString());
				return query.addScalar("totalCount", Hibernate.INTEGER).uniqueResult();
			}
		});
		
		Page page = new Page();
		page.setTotalCount(totalCount);
		page.setPageNo(curPage);
		page.setPageSize(pageSize);
		page.setResult(list);
		
		return page;
		
	}
	
	/**
	 *@Todo:分页查找名家作品(非随机)
	 *@CreateTime:2011-12-22 上午09:55:17
	 * @param type 作品类型(1:油画 2:国画 null:不指定类型)
	 */
	public Page findPaintingsByPageOfPainterStar(String type,int curPage,int pageSize) {
        UploadState  uploadState = uploadStateManager.findUniqueBy("english_name", Constants.Upload_State_Init); //上传状态正常
        AuditState auditState = auditStateManager.findUniqueBy("english_name", Constants.Audit_State_Pass);  //审核状态通过
		
		String hql = " from Painting p where p.uploadState= ?  and p.auditState= ? and p.painter.type = 2 ";
		
		if("1".equals(type)){//油画
			hql += " and ( p.painter.decade = 2  or p.painter.decade = 4)";
		}
		else if("2".equals(type)){//国画
			hql += " and ( p.painter.decade = 1  or p.painter.decade = 3 or p.painter.decade = 5 )";
		}
		
		hql += " order by p.id desc ";
		
		return super.pagedQuery(hql,curPage, pageSize,uploadState,auditState);
	}

	/**
	 * @param curPage
	 * @param pageSize
	 * @param type 瓷艺类型  1：真迹 2：高仿
	 */
	public Page findCiYiByPage(Integer curPage, Integer pageSize,String type) {
		UploadState  uploadState = uploadStateManager.findUniqueBy("english_name", Constants.Upload_State_Init); //上传状态正常
        AuditState auditState = auditStateManager.findUniqueBy("english_name", Constants.Audit_State_Pass);  //审核状态通过
        CatalogType catalogType = catalogTypeManager.findUniqueBy("english_name", Constants.CIYI);//瓷艺
        
        if(type == null){
    	   String hql = " from Painting p where p.uploadState= ?  and p.auditState= ? and p.catalogType = ? order by p.id desc";
           return super.pagedQuery(hql, curPage, pageSize, uploadState,auditState,catalogType);
        }else{
    	   String hql = " from Painting p where p.uploadState= ?  and p.auditState= ? and p.catalogType = ? and p.remark = ? order by p.id desc";
           return super.pagedQuery(hql, curPage, pageSize, uploadState,auditState,catalogType,type);
        }
	}
	
	/**
	 * @param curPage
	 * @param pageSize
	 * @param type 1：真迹 2：高仿
	 */
	public Page findShuhuaByPage(Integer curPage, Integer pageSize,String type) {
		UploadState  uploadState = uploadStateManager.findUniqueBy("english_name", Constants.Upload_State_Init); //上传状态正常
        AuditState auditState = auditStateManager.findUniqueBy("english_name", Constants.Audit_State_Pass);  //审核状态通过
        
        if(type == null){
    	   String hql = " from Painting p where p.uploadState= ?  and p.auditState= ? and ( p.catalogType.id = 1 or p.catalogType.id = 2 ) order by p.id desc";
           return super.pagedQuery(hql, curPage, pageSize, uploadState,auditState);
        }else{
    	   String hql = " from Painting p where p.uploadState= ?  and p.auditState= ? and ( p.catalogType.id = 1 or p.catalogType.id = 2 ) and p.remark = ? order by p.id desc";
           return super.pagedQuery(hql, curPage, pageSize, uploadState,auditState,type);
        }
	}
	
	/**
	 * 分页查找可用的作品(排序)
	 * @param curPage 当前页码
	 * @param pageSize 当前页面size
	 * @param paintingtype 作品类型
	 */
	public Page findAvailablePaintingsByPage(final Integer curPage,final Integer pageSize,final String paintingtype){
		List list  =  (List)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				StringBuilder sql = new StringBuilder("select a.* from t_painting a where a.auditstate_id = 3 and a.uploadstate_id = 1 ");
				if( paintingtype !=null &&  !"".equals(paintingtype)){
					sql.append(" and a.catalogtype_id = " + paintingtype);
				}else{
					sql.append(" and ( a.catalogtype_id = 1 or a.catalogtype_id = 2 ) ");
				}
				sql.append(" order by case when a.order_no IS NULL THEN 1 ELSE 0 END,a.order_no,a.sortflag desc,a.id desc ");
				
				SQLQuery query = session.createSQLQuery(sql.toString());
				query.setFirstResult((curPage-1)*pageSize);
				query.setMaxResults(pageSize);
				
				List list = query.addEntity(Painting.class).list();
				
				return list;
			}
		});
		
		Integer totalCount  =  (Integer)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				StringBuilder sql = new StringBuilder("select count(*) as totalCount from t_painting a where a.auditstate_id = 3 and a.uploadstate_id = 1 ");
				
				if( paintingtype !=null &&  !"".equals(paintingtype)){
					sql.append(" and a.catalogtype_id = " + paintingtype);
				}else{
					sql.append(" and ( a.catalogtype_id = 1 or a.catalogtype_id = 2 ) ");
				}
				
				SQLQuery query = session.createSQLQuery(sql.toString());
				return query.addScalar("totalCount", Hibernate.INTEGER).uniqueResult();
			}
		});
		
		Page page = new Page();
		page.setTotalCount(totalCount);
		page.setPageNo(curPage);
		page.setPageSize(pageSize);
		page.setResult(list);
		
		return page;
	}
	
	/**
	 * 分页查找可用的作品(排序),按画家分类
	 * @param curPage 当前页码
	 * @param pageSize 当前页面size
	 * @param paintingtype 作品类型
	 */
	public Page findAvailablePaintingsSortOfPainterByPage(final Integer curPage,final Integer pageSize,final String paintingtype){
		List list  =  (List)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				StringBuilder sql = new StringBuilder("select p.* from (select a.id from t_painter a where a.auditstate_id = 3 and a.uploadstate_id = 1 ");
				if( paintingtype !=null &&  !"".equals(paintingtype)){
					sql.append( " and a.catalogtype_id = " + paintingtype);
				}else{
					sql.append(" and ( a.catalogtype_id = 1 or a.catalogtype_id = 2 ) ");
				}
				sql.append(" order by case when a.order_no IS NULL THEN 1 ELSE 0 END,a.order_no, a.sortflag desc, a.id desc ) b ");
				
				sql.append(" left join ( select max(id) as max_id,painterid as pid from t_painting  group by painterid order by id desc ) c  on b.id = c.pid ");
				sql.append(" left join t_painting p on p.id = c.max_id ");
				
				SQLQuery query = session.createSQLQuery(sql.toString());
				query.setFirstResult((curPage-1)*pageSize);
				query.setMaxResults(pageSize);
				
				List list = query.addEntity(Painting.class).list();
				
				return list;
			}
		});
		
		Integer totalCount  =  (Integer)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				StringBuilder sql = new StringBuilder("select  count(*) as totalCount  from (select a.id from t_painter a where a.auditstate_id = 3 and a.uploadstate_id = 1 ");
				
				if( paintingtype !=null &&  !"".equals(paintingtype)){
					sql.append(" and a.catalogtype_id = " + paintingtype);
				}else{
					sql.append(" and ( a.catalogtype_id = 1 or a.catalogtype_id = 2 ) ");
				}
				sql.append(") b ");
				
				sql.append(" left join ( select max(id) as max_id,painterid as pid from t_painting  group by painterid order by id desc ) c  on b.id = c.pid ");
				
				SQLQuery query = session.createSQLQuery(sql.toString());
				return query.addScalar("totalCount", Hibernate.INTEGER).uniqueResult();
			}
		});
		
		Page page = new Page();
		page.setTotalCount(totalCount);
		page.setPageNo(curPage);
		page.setPageSize(pageSize);
		page.setResult(list);
		
		return page;
	}

	/**
	 * 查找相关瓷艺作品(您可能喜欢)
	 * @param pId 作品ID
	 * @param pageSize 结果集大小
	 */
	@SuppressWarnings("unchecked")
	public List findRelativePaintingList(Long pId, Integer pageSize,Long catalog_id) {
		
		UploadState  uploadState = uploadStateManager.findUniqueBy("english_name", Constants.Upload_State_Init); //上传状态正常
        AuditState auditState = auditStateManager.findUniqueBy("english_name", Constants.Audit_State_Pass);  //审核状态通过
        
        String hql = " from Painting p where p.id < ? and  p.uploadState= ?  and p.auditState= ? and p.catalogType.id = ? order by p.id desc";
        
        Page page =  super.pagedQuery(hql, 1, pageSize,pId, uploadState,auditState,catalog_id);
        
        Object result = page.getResult();
        if( result != null ){
        	
        	List list = (List)page.getResult();
        	if( list.size() < 1){
        		 hql = " from Painting p where p.uploadState= ?  and p.auditState= ? and p.catalogType.id = ? order by p.id desc";
        		 page =  super.pagedQuery(hql, 1, pageSize,uploadState,auditState,catalog_id);
        		 if( page.getResult()!= null){
        			 return (List)page.getResult();
        		 }
        	}else{
        		return list;
        	}
        }
       
       return null;
	}

	/**
	 * 查找瓷艺频道的推荐作品
	 */
	@SuppressWarnings("unchecked")
	public Painting findRecommendPainting() {
		
		UploadState  uploadState = uploadStateManager.findUniqueBy("english_name", Constants.Upload_State_Init); //上传状态正常
        AuditState auditState = auditStateManager.findUniqueBy("english_name", Constants.Audit_State_Pass);  //审核状态通过
        CatalogType catalogType = catalogTypeManager.findUniqueBy("english_name", Constants.CIYI);//瓷艺
        
        Painting painting = null;
        
        String hql = " from Painting p where p.onSaleFlag = '2' and p.uploadState= ?  and p.auditState= ? and p.catalogType = ? and p.qqxl is not null order by p.qqxl asc, p.price desc";
        Page page =  super.pagedQuery(hql, 1, 1,uploadState,auditState,catalogType);
        List list = (List)page.getResult();
        if( list != null && list.size() > 0){
        	painting = (Painting)list.get(0);
        }
        
        String hql2 = " from Painting p where p.onSaleFlag = '1' and  p.uploadState= ?  and p.auditState= ? and p.catalogType = ? and p.qqxl is not null order by p.qqxl asc, p.bidPrice desc";
        Page page2 =  super.pagedQuery(hql2, 1, 1,uploadState,auditState,catalogType);
        List list2 = (List)page2.getResult();
        if( list2 != null && list2.size() > 0 ){
        	 Painting bid_painting = (Painting)list2.get(0);
        	 if( bid_painting.getBidPrice() != null){
        		 if(bid_painting.getBidPrice() > painting.getPrice()){
        			 painting = bid_painting;
        		 }
        	 }
        }
        
        return painting;
	}

	/**
	 * 查找最新售卖作品
	 * @param size
	 */
	@SuppressWarnings("unchecked")
	public List findNewestSale(int size) {
		String hql = "from Painting p where p.onSaleFlag<>'0' and (p.price >0 or p.bidPrice > 0) order by p.id desc";
		Page page = super.pagedQuery(hql, 1, size);
		if( page.getResult()!=null){
			Object object = page.getResult();
			return (List)object;
		}
		return null;
	}
	
	/**
	 * 查找画家所有作品(排序)
	 * @param painterId 画家ID
	 */
	@SuppressWarnings("unchecked")
	public List<Painting> findPaintingsByPainter(final Long painterId){
		
		List list  =  (List)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				StringBuilder sql = new StringBuilder("select a.* from t_painting a where a.painterid=?");
			
				sql.append(" order by case when a.order_no IS NULL THEN 1 ELSE 0 END,a.order_no,a.sortflag desc,a.id desc ");
				
				
				SQLQuery query = session.createSQLQuery(sql.toString());
				query.setParameter(0, painterId);
				
				List list = query.addEntity(Painting.class).list();
				
				return list;
			}
		});
		
		return list;
	}

	/**
	 * 查找前一幅作品及后一幅作品ID
	 * @param id 当前作品id
	 */
	public Long[] findPreAndNextId(final Long id) {
		Long[] idArray = (Long[]) this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Long[] ids = new Long[2];
				String preSql = "select id from t_painting where id < " + id + " and catalogtype_id is not null order by id desc limit 0,1";
				SQLQuery query = session.createSQLQuery(preSql);
				ids[0] = (Long) query.addScalar("id", Hibernate.LONG).uniqueResult();
				
				String nextSql = "select id from t_painting where id > " + id + " and catalogtype_id is not null order by id asc limit 0,1";
				query = session.createSQLQuery(nextSql);
				
				ids[1] = (Long) query.addScalar("id", Hibernate.LONG).uniqueResult();
				
				return ids;
			}
		});
		
		return idArray;
	}
	
}
